--------------------------------------------------------
--  File created - Friday-November-20-2015   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Procedure PR_RAISE_ERR
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "PR_RAISE_ERR" 
                           (p_fn_name  VARCHAR2
                           ,p_err_msg  VARCHAR2
                           ,p_err_line VARCHAR2) 
IS                                     PRAGMA AUTONOMOUS_TRANSACTION;  
BEGIN
  INSERT INTO tb_app_error
             (name
             ,msg
             ,line)
  VALUES     (p_fn_name
             ,p_err_msg
             ,p_err_line);
  COMMIT;                      
END PR_RAISE_ERR;

/
--------------------------------------------------------
--  DDL for Procedure PR_UPDATE_DEL_RDC_HIST_IF
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "PR_UPDATE_DEL_RDC_HIST_IF" (v_inv_org varchar2, v_delivery_no varchar2, v_plan_no varchar2,
                                                  v_pick_no varchar2, v_pick_date varchar2, v_shipto_code varchar2,
                                                  v_shipto_name varchar2, v_ship_date varchar2, v_model_suffix varchar2,
                                                  v_delivery_address varchar2, v_delivery_qty varchar2, v_delivery_date varchar2,
                                                  v_delivery_status varchar2, v_line_id varchar2) is
begin
  insert into vnxx_delivery_rdc_hist_if
              (inv_org,
               delivery_no,
               plan_no,
               pick_no,
               pick_date,
               shipto_code,
               shipto_name,
               ship_date,
               model_suffix,
               delivery_address,
               delivery_qty,
               delivery_date,
               delivery_status,
               line_id)
  values
              (v_inv_org,
               v_delivery_no,
               v_plan_no,
               v_pick_no,
               to_date(v_pick_date,'YYYYMMDD'),
               v_shipto_code,
               v_shipto_name,
               to_date(v_ship_date,'YYYYMMDD'),
               v_model_suffix,
               v_delivery_address,
               v_delivery_qty,
               to_date(v_delivery_date,'YYYYMMDD'),
               v_delivery_status,
               v_line_id);
exception
  when DUP_VAL_ON_INDEX then null;

end PR_UPDATE_DEL_RDC_HIST_IF;

/
--------------------------------------------------------
--  DDL for Procedure READ_FILE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "READ_FILE" 
IS
 f utl_file.file_type;
 s varchar2(200);
BEGIN
 f := utl_file.fopen('MYDIR','dbms_logs.log','R');
 LOOP
   BEGIN
     UTL_FILE.GET_LINE(f,s);
     DBMS_OUTPUT.PUT_LINE(s);
   EXCEPTION
     WHEN NO_DATA_FOUND THEN EXIT ;
   END;
END LOOP;
   utl_file.fclose(f);
END read_file;

/
--------------------------------------------------------
--  DDL for Procedure SMS_SHOP
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SMS_SHOP" 

is
 v_msgbody varchar2(200) := null;
begin

for cur1 in (select *
from tb_cm_shop_inf
where shop_code in ('EASV0106','EASV0674','EASV0644','EASV0643','EASV0642','EASV0641',
'EASV0640','EASV0639','EASV0638','EASV0637','EASV0636','EASV0635',
'EASV0634','EASV0633','EASV0630','EASV0629','EASV0627','EASV0626',
'EASV0625','EASV0624','EASV0623','EASV0089','EASV0098','EASV0099',
'EASV0663','EASV0662','EASV0661','EASV0660','EASV0659','EASV0658',
'EASV0657','EASV0656','EASV0655','EASV0622','EASV0093','EASV0463',
'EASV0462','EASV0461','EASV0460','EASV0459','EASV0458','EASV0457',
'EASV0456','EASV0455','EASV0454','EASV0453','EASV0326','EASV0101',
'EASV0017','EASV0677','EASV0676','EASV0675','EASV0654','EASV0653',
'EASV0652','EASV0651','EASV0650','EASV0649','EASV0648','EASV0647',
'EASV0646','EASV0645','EASV0632','EASV0631','EASV0628','EASV0094',
'EASV0318','EASV0109','EASV0096','EASV0107','EASV0095','EASV0016',
'EASV0005','EASV0103','EASV0008','EASV0007','EASV0006','EASV0090',
'EASV0108','EASV0102','EASV0092','EASV0097','EASV0603','EASV0601',
'EASV0600','EASV0599','EASV0598','EASV0597','EASV0596','EASV0595',
'EASV0594','EASV0593','EASV0592','EASV0591','EASV0590','EASV0589',
'EASV0588','EASV0587','EASV0586','EASV0585','EASV0584','EASV0583',
'EASV0582','EASV0581','EASV0580','EASV0579','EASV0578','EASV0577',
'EASV0576','EASV0575','EASV0574','EASV0573','EASV0572','EASV0571',
'EASV0570','EASV0569','EASV0568','EASV0567','EASV0566','EASV0565',
'EASV0564','EASV0563','EASV0562','EASV0561','EASV0560','EASV0559',
'EASV0558','EASV0557','EASV0556','EASV0555','EASV0554','EASV0553',
'EASV0552','EASV0551','EASV0550','EASV0549','EASV0548','EASV0547',
'EASV0546','EASV0545','EASV0544','EASV0543','EASV0542','EASV0541',
'EASV0540','EASV0539','EASV0538','EASV0537','EASV0536','EASV0535',
'EASV0534','EASV0533','EASV0532','EASV0531','EASV0530','EASV0529',
'EASV0528','EASV0527','EASV0526','EASV0525','EASV0524','EASV0523',
'EASV0522','EASV0521','EASV0520','EASV0519','EASV0518','EASV0517',
'EASV0516','EASV0515','EASV0514','EASV0513','EASV0512','EASV0511',
'EASV0510','EASV0509','EASV0508','EASV0507','EASV0506','EASV0505',
'EASV0504','EASV0503','EASV0502','EASV0501','EASV0500','EASV0499',
'EASV0498','EASV0497','EASV0496','EASV0495','EASV0494','EASV0493',
'EASV0040','EASV0039','EASV0038','EASV0037','EASV0036','EASV0035',
'EASV0034','EASV0033','EASV0032','EASV0029','EASV0028','EASV0026',
'EASV0025','EASV0673','EASV0672','EASV0671','EASV0670','EASV0669',
'EASV0668','EASV0667','EASV0666','EASV0665','EASV0664','EASV0621',
'EASV0620','EASV0619','EASV0618','EASV0617','EASV0616','EASV0615',
'EASV0614','EASV0613','EASV0612','EASV0611','EASV0610','EASV0609',
'EASV0608','EASV0607','EASV0606','EASV0091','EASV0104','EASV0105'))
loop
  IF cur1.CELL_NO#1 IS NOT NULL AND cur1.CELL_NO#2 IS NOT NULL THEN
      v_msgbody := pk_sms_mst.FN_GET_MT_RESPONSE(pk_sms_mst.const_shp_reg_su, '$'||'8069');

      INSERT INTO tb_mt_hist (moseq, cell_no      , cmdcode, msgbody  , resp_type)
      VALUES                 (0    ,cur1.CELL_NO#1, 'LGDK' , v_msgbody, pk_sms_mst.const_shp_reg_su);

      INSERT INTO tb_mt_hist (moseq, cell_no      , cmdcode, msgbody  , resp_type)
      VALUES                 (0    ,cur1.CELL_NO#2, 'LGDK' , v_msgbody, pk_sms_mst.const_shp_reg_su);
    ELSIF cur1.CELL_NO#1 IS NOT NULL THEN
      v_msgbody := pk_sms_mst.FN_GET_MT_RESPONSE(pk_sms_mst.const_shp_reg_su, '$'||'8069');

      INSERT INTO tb_mt_hist (moseq, cell_no      , cmdcode, msgbody  , resp_type)
      VALUES                 (0    ,cur1.CELL_NO#1, 'LGDK' , v_msgbody, pk_sms_mst.const_shp_reg_su);
    ELSIF cur1.CELL_NO#2 IS NOT NULL THEN
      v_msgbody := pk_sms_mst.FN_GET_MT_RESPONSE(pk_sms_mst.const_shp_reg_su, '$'||'8069');

      INSERT INTO tb_mt_hist (moseq, cell_no      , cmdcode, msgbody  , resp_type)
      VALUES                 (0    ,cur1.CELL_NO#2, 'LGDK' , v_msgbody, pk_sms_mst.const_shp_reg_su);
    END IF;
end loop;
end;

/
--------------------------------------------------------
--  DDL for Procedure TEST0
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "TEST0" (out_cur out SYS_REFCURSOR, region_id in varchar2 default '01') 
is
begin
 open out_cur for
      select * 
      from   tb_cm_shop_inf
      where reg_id = region_id;
end TEST0;



/
--------------------------------------------------------
--  DDL for Procedure WRITE_LOG
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "WRITE_LOG" AS
  l_line     VARCHAR2(255);
  l_done     NUMBER;
  l_filename varchar2(50) := TO_CHAR(sysdate,'YYYYMMDD')||'_logs.log';
  l_file utl_file.file_type;
BEGIN
   l_file := utl_file.fopen('MYDIR', l_filename, 'A');
   LOOP
      EXIT WHEN l_done = 1;
      dbms_output.get_line(l_line, l_done);
      utl_file.put_line(l_file, l_line);
   END LOOP;
  utl_file.fflush(l_file);
  utl_file.fclose(l_file);
END write_log;

/
